use "C:\Subprovince Database 1"
drop if year==2005
collapse (sum) pd,by(admin year)
replace pd=100*pd
keep pd admin year
sort admin year
reshape wide pd, i(admin) j(year)
sort admin
merge admin using "C:\SE Asia Admin Names"
drop _merge
replace pd2006=0 if pd2006==.
replace pd2007=0 if pd2007==.
replace pd2008=0 if pd2008==.
replace pd2009=0 if pd2009==.
replace pd2010=0 if pd2010==.
egen r2006=rank(pd2006),unique
egen r2007=rank(pd2007),unique
egen r2008=rank(pd2008),unique
egen r2009=rank(pd2009),unique
egen r2010=rank(pd2010),unique
gen n=_n
egen maxn=max(n)
replace r2006=(maxn+1)-r2006
replace r2007=(maxn+1)-r2007
replace r2008=(maxn+1)-r2008
replace r2009=(maxn+1)-r2009
replace r2010=(maxn+1)-r2010

gen cpd=pd2010/pd2006
replace cpd=1 if cpd==.
sort admin
save "C:\Subprovince Mapping Data",replace
outsheet admin pd* r* cpd using "C:\mapper",replace
 

